package org.languageguide.readings;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.languageguide.storage.DBConnector;
import org.languageguide.user.User;
import org.languageguide.versioncontrol.LockingVersionControl;
import org.languageguide.versioncontrol.VersionControlUtil;

/**
 * Used to represent a "translated" readings, one for which definitions and translations
 * have been added.  
 * 
 * @author Tom Blackmon
 */

public class TranslatedReadingImpl implements TranslatedReading, LockingVersionControl{

	private String id;
	private String language;
	private String targetLanguage;
	//private boolean loaded = false;
	private String text;
	private String title;
	private String status;
	private String translatedText;
	private Boolean checkedOut;
	private Boolean approvedSentences;
	private Boolean approvedDefinitions;
	private String imageTimes;
///	private Date checkoutTime;
//	private String userId;
	
	public boolean hasApprovedSentences(){
		return approvedSentences;
	}
	
	public boolean hasApprovedDefinitions(){
		return approvedDefinitions;
	}
	
	public TranslatedReadingImpl(ResultSet set, String targetLanguage) throws SQLException{
		processRowLite(set);
		this.targetLanguage = targetLanguage;
	}

	public TranslatedReadingImpl(String id, String targetLanguage){
		this.id = id;
		this.targetLanguage = targetLanguage;
		loadReadingFromDb();

	}
	
	public Integer getId(){
		return Integer.parseInt(id);
	}

	@Override
	public boolean checkin(User user) {
		Integer userId = user.getIdInteger();
		//System.out.println("userId" + userId);
		String statement = "UPDATE TranslatedReading SET CheckedOut = 0 WHERE ReadingId = ? AND TargetLanguage = ? AND EditingUserId = ?";
		
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			//System.out.println("creating statement");
			PreparedStatement ps = connection.prepareStatement(statement);
			ps.setString(1, id);
			ps.setString(2, targetLanguage);
			ps.setInt(3, userId);
			//System.out.println("setting items");
			int rows = ps.executeUpdate();
			//System.out.println("update called" + rows);
			if (rows > 0)
				return true;
			else return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}

		return false;
	}

	@Override
	public boolean checkout(User user) {
		System.out.println("hasTrans" + hasTranslation);
		Integer userId = user.getIdInteger();
		if (hasTranslation){
			//			SELECT * From TranslatedReading  4
			String statement = "UPDATE TranslatedReading SET CheckedOut = 1, CheckoutTime = NULL, EditingUserId = ? WHERE ReadingId = ? AND TargetLanguage = ? AND (CheckedOut = 0 OR (DATE_ADD(CheckoutTime, INTERVAL 1 HOUR) < NOW())) OR EditingUserId = ?";
			DBConnector connector = DBConnector.getInstance();
			Connection connection = connector.getConnection();
			try {
				PreparedStatement ps = connection.prepareStatement(statement);
				ps.setInt(1, userId);
				ps.setString(2, id);
				ps.setString(3, targetLanguage);
				ps.setInt(4, userId);
				int rows = ps.executeUpdate();
				if (rows > 0)
					return true;
				else return false;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				connector.close(connection);
			}
			return false;
		}
		else{
			String statement = "INSERT INTO TranslatedReading (ReadingId, TargetLanguage, EditingUserId, CheckedOut) Values (?, ?, ?, ?)";
			DBConnector connector = DBConnector.getInstance();
			Connection connection = connector.getConnection();
			try {
				PreparedStatement ps = connection.prepareStatement(statement);
				ps.setString(1, id);
				ps.setString(2, targetLanguage);
				ps.setInt(3, userId);
				ps.setBoolean(4, true);
				int rows = ps.executeUpdate();
				if (rows > 0){
					hasTranslation = true;
					return true;
				}
				else return false;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				connector.close(connection);
			}
			return false;
		}
	}

	/* if a translated version of the reading doesn't exist, then 
	 * return the original reading
	 */
	public String getText() {
		if (translatedText != null){
			return translatedText;
		}
		else{
			SentenceWrapper wrappar = new StandardSentenceWrapper();
			return wrappar.wrap(text);
		}
	}

	@Override
	public String getTitle() {
		return title;
	}

	public String getStatus(){
		if (status == null)
			return "New";
		return status;
	}
	
	public boolean isPublished(){
		if (status != null && status.equals("Published"))
			return true;
		return false;
	}
	
	public boolean save(User user, String text, String status, boolean checkin) {
		Integer userId = user.getIdInteger();
		String sqlBackup = "INSERT INTO TranslatedReadingHistory SELECT * FROM TranslatedReading WHERE ReadingId = \"" + id + "\" AND TargetLanguage = \"" + targetLanguage + "\" ";
		String updateSql = "UPDATE TranslatedReading SET Text = ?, UserId = ?, Status = ?, CheckedOut=?, CheckoutTime = NULL WHERE ReadingId = ? AND TargetLanguage = ? AND EditingUserId = ?";
		
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			Statement statement = connection.createStatement();
			statement.executeUpdate(sqlBackup);
			//String statement = "UPDATE TranslatedReading SET CheckedOut = 1, CheckoutTime = NULL, UserId = ? WHERE ReadingId = ? AND TargetLanguage = ? ";
			PreparedStatement ps = connection.prepareStatement(updateSql);
			System.out.println("updateSql" + updateSql);
			ps.setString(1, text);
			ps.setInt(2, userId);
			System.out.println("userId" + userId);
			ps.setString(3, status);
			System.out.println("status" + status);
			ps.setBoolean(4, !checkin);
			System.out.println("checkin" + checkin);
			ps.setString(5, id);
			System.out.println("id" + id);
			ps.setString(6, targetLanguage);
			System.out.println("targetLanguage" + targetLanguage);
			ps.setInt(7, userId);
			System.out.println("userId" + userId);
			int rows = ps.executeUpdate();
			if (rows > 0){
				return true;
			}
			else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("exception" + e.getMessage());
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return false;

	}

	boolean hasTranslation =  false;
	
	
	private void loadReadingFromDb(){
		String sql_load = "SELECT * FROM Readings LEFT JOIN TranslatedReading ON Readings.Id = TranslatedReading.ReadingId AND TranslatedReading.TargetLanguage = \"" + targetLanguage + "\" WHERE Readings.Id = \"" + id + "\"";
		System.out.println("sql_load:" + sql_load);
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql_load);
			while (set.next()){
				processRow(set);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
	}
	
	
	public boolean publish(){
		ReadingDivider readingDivider = new ReadingDivider(translatedText);
		
		WordWrapper wrappedReading = new StandardWordWrapper();
		String textWordWrapped = wrappedReading.wrap(readingDivider.getBody());
		String textWordWrappedAndWithTranslations = textWordWrapped + readingDivider.getTranslations();
		
		String updateSql = "UPDATE TranslatedReading SET PublishedText = ? WHERE ReadingId = ? AND TargetLanguage = ?";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(updateSql);
			ps.setString(1, textWordWrappedAndWithTranslations);
			ps.setString(2, id);
			ps.setString(3, targetLanguage);
			int rows = ps.executeUpdate();
			if (rows > 0)
				return true;
			else return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		finally{
			connector.close(connection);
		}
		return false;
		
	}
	
	
	
	//private String sentenceWrappedReading;
	private void processRowLite(ResultSet set) throws SQLException{
		id = set.getString("Readings.Id");
		title = set.getString("Readings.Title");
		//language = set.getString("Readings.Language");
		//System.out.println("language" + language);
		
		status = set.getString("TranslatedReading.Status");
		
		checkedOut = set.getBoolean("TranslatedReading.CheckedOut");
		Date checkoutTime = set.getDate("TranslatedReading.CheckoutTime");
		System.out.println("checkedOut" + checkoutTime + " " + checkedOut);
		if (checkoutTime != null){
			hasTranslation = true;
			if (checkedOut){
				//Date checkoutTime = set.getDate("TranslatedReading.CheckoutTime");
				if (VersionControlUtil.hasCheckoutExpired(checkoutTime)){
					checkedOut = false;
				}
			}
		}
		System.out.println("hasTranslation" +hasTranslation);

		//userId = set.getString("TranslatedReading.UserId");
		//loaded = true;

	}

		
	//private String sentenceWrappedReading;
	private void processRow(ResultSet set) throws SQLException{
		text = set.getString("Readings.Text");

		/*sentenceWrappedReading = set.getString("Readings.SentenceWrappedText");
		if (sentenceWrappedReading == null){
			SentenceWrapper wrapper = new SentenceWrapper();
			sentenceWrappedReading = wrapper.prepare(text);
		}*/


		id = set.getString("Readings.Id");
		title = set.getString("Readings.Title");
		language = set.getString("Readings.Language");
		System.out.println("language" + language);
		String targetLanguageFromDb = set.getString("TranslatedReading.TargetLanguage");
		if (targetLanguageFromDb != null){
			targetLanguage = targetLanguageFromDb;
		}
		status = set.getString("TranslatedReading.Status");
		translatedText = set.getString("TranslatedReading.Text");
		checkedOut = set.getBoolean("TranslatedReading.CheckedOut");
		approvedSentences = set.getBoolean("TranslatedReading.ApprovedSentences");
		approvedDefinitions = set.getBoolean("TranslatedReading.ApprovedDefinitions");
		imageTimes = set.getString("TranslatedReading.ImageTimes");		
		Date checkoutTime = set.getDate("TranslatedReading.CheckoutTime");
		System.out.println("checkedOut" + checkoutTime + " " + checkedOut);
		if (checkoutTime != null){
			hasTranslation = true;
			if (checkedOut){
				//Date checkoutTime = set.getDate("TranslatedReading.CheckoutTime");
				if (VersionControlUtil.hasCheckoutExpired(checkoutTime)){
					checkedOut = false;
				}
			}
		}
		System.out.println("hasTranslation" +hasTranslation);

		//userId = set.getString("TranslatedReading.UserId");
		//loaded = true;

	}

	public String getLanguage(){
		return language;
	}
	
	public String getTargetLanguage(){
		return targetLanguage;
	}
	
	public String getURL(){
		return "/readings/view.jsp?id=" + id + "&target=" + targetLanguage;		
	}
	
	public String getNewXHTMLTableRow(){
		checkedOut = false;
		if (!checkedOut){
			String statusToDisplay;
			if (status == null){
				statusToDisplay= "New";
			}
			else{
				statusToDisplay= status;	
			}
			return "<tr><td><a href=\"/user/readings/translate_reading.jsp?id=" +  id + "&target=" + targetLanguage + "\">" + title + "</a></td><td>" + statusToDisplay + "</td></tr>";
		}
		else{
			return "<tr><td>" + title + "</td><td>Checked Out</td></tr>";
		}
		
	}

	public String getXHTMLTableRow(){
		checkedOut = false;
		if (!checkedOut){
			return "<tr><td><a href=\"edit.jsp?id=" +  id + "&target=" + targetLanguage + "\">" + title + "</a></td><td>" + status + "</td></tr>";
		}
		else{
			return "<tr><td>" + title + "</td><td>Checked Out</td></tr>";
		}
	}

	public static void main(String[] args) {
		//addReading("My Name is Barney", "singlurity", "fr");
		
		 
		TranslatedReading reading = new TranslatedReadingImpl("45", "it");
		reading.getText();
	}
}